package com.huxi.lang.file.xls;

import java.sql.Timestamp;
import java.time.DateTimeException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.huxi.lang.local.DateUtils;
import com.huxi.lang.type.BooleanParseException;

public class XlsUtils {

	/**
	 * 获取单元格内容且设置新值
	 * 
	 * @param cell  单元格
	 * @param value 新值
	 * @return 前单元格值
	 */
	public static Object getAndSetCellValue(Cell cell, Object value) {
		Object vv = getCellValue(cell);
		setCellValue(cell, value);
		return vv;
	}

	/**
	 * 设置单元格内容
	 * 
	 * @param cell  单元格，非空。
	 * @param value 单元值
	 */
	public static void setCellValue(Cell cell, Object value) {
		if (value instanceof Number) {
			cell.setCellValue(((Number) value).doubleValue());
		} else if (value instanceof CharSequence) {
			cell.setCellValue(value.toString());
		} else if (value instanceof Date) {
			cell.setCellValue((Date) value);
		} else if (value == null) {
			cell.setBlank();
		} else if (value instanceof Boolean) {
			cell.setCellValue((Boolean) value);
		} else if (value instanceof LocalDate) {
			cell.setCellValue((LocalDate) value);
		} else if (value instanceof LocalDateTime) {
			cell.setCellValue((LocalDateTime) value);
		} else if (value instanceof RichTextString) {
			cell.setCellValue((RichTextString) value);
		} else {
			cell.setCellValue(value.toString());
		}
	}

	/**
	 * 获取单元格内容
	 * <ul>
	 * <li>空白单元格直接返回空值{@code null}；</li>
	 * <li>日期类型数据返回的时时间戳长整数；</li>
	 * <li>无小数位数字则返回长整数；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 单元值
	 */
	public static Object getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.STRING == ct) {
			return cell.getStringCellValue();
		} else if (CellType.NUMERIC == ct) {
			return doubleToNumber(cell.getNumericCellValue());
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.FORMULA == ct) {
			CellType mct = cell.getCachedFormulaResultType();
			if (CellType.NUMERIC == mct) {
				return doubleToNumber(cell.getNumericCellValue());
			} else if (CellType.STRING == mct) {
				return cell.getStringCellValue();
			} else if (CellType.BOOLEAN == mct) {
				return cell.getBooleanCellValue();
			} else {
				return null; // 计算失败或者其它情况返回空值
			}
		} else if (CellType.BOOLEAN == ct) {
			return cell.getBooleanCellValue();
		} else if (CellType.ERROR == ct) {
			return cell.getErrorCellValue();
		} else {
			return cell.getStringCellValue();
		}
	}

	private static CellValue evaluate(Cell cell) {
		Workbook wb = cell.getRow().getSheet().getWorkbook();
		if (wb instanceof XSSFWorkbook) {
			return ((XSSFWorkbook) wb).getCreationHelper().createFormulaEvaluator().evaluate(cell);
		} else {
			// TODO
			throw new RuntimeException("暂不支持");
		}
	}

	/**
	 * 获取单元格计算后内容
	 * <ul>
	 * <li>空白单元格直接返回空值{@code null}；</li>
	 * <li>日期类型数据返回的时时间戳长整数；</li>
	 * <li>无小数位数字则返回长整数；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 单元值
	 */
	public static Object evaluateCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.STRING == ct) {
			return cell.getStringCellValue();
		} else if (CellType.NUMERIC == ct) {
			return doubleToNumber(cell.getNumericCellValue());
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.FORMULA == ct) {
			CellValue mcv = evaluate(cell);
			CellType mct = mcv.getCellType();
			if (CellType.NUMERIC == mct) {
				return doubleToNumber(mcv.getNumberValue());
			} else if (CellType.STRING == mct) {
				return mcv.getStringValue();
			} else if (CellType.BOOLEAN == mct) {
				return mcv.getBooleanValue();
			} else {
				return null; // 计算失败或者其它情况返回空值
			}
		} else if (CellType.BOOLEAN == ct) {
			return cell.getBooleanCellValue();
		} else if (CellType.ERROR == ct) {
			return cell.getErrorCellValue();
		} else {
			return cell.getStringCellValue();
		}
	}

	private static Number doubleToNumber(double value) {
		long nn = (long) value;
		return value == nn ? Long.valueOf(nn) : Double.valueOf(value);
	}

	private static String doubleToText(double value) {
		long nn = (long) value;
		return value == nn ? String.valueOf(nn) : String.valueOf(value);
	}

	/**
	 * 获取单元格字符串内容
	 * <ul>
	 * <li>空白单元格直接返回空字符串；</li>
	 * <li>布尔类型返回true|false；</li>
	 * <li>无小数位数字则返回整数数字；</li>
	 * <li>计算单元返回计算公式；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 字符串值
	 */
	public static String getCellStringValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.STRING == ct) {
			return cell.getStringCellValue();
		} else if (CellType.NUMERIC == ct) {
			return doubleToText(cell.getNumericCellValue());
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.FORMULA == ct) {
			return cell.getCellFormula();
		} else if (CellType.BOOLEAN == ct) {
			return Boolean.valueOf(cell.getBooleanCellValue()).toString();
		} else if (CellType.ERROR == ct) {
			return String.valueOf(cell.getErrorCellValue());
		} else {
			return cell.getStringCellValue();
		}
	}

	/**
	 * 获取单元格双浮点数内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为数字，非数字文本则抛出非数字异常；</li>
	 * <li>布尔类型直接抛出非数字异常；</li>
	 * <li>错误类型直接抛出非数字异常；</li>
	 * <li>无小数位数字则返回整数数字；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 双浮点数值
	 * @throws NumberFormatException 非数字或者文本解析数字失败
	 */
	public static Double getCellDoubleValue(Cell cell) throws NumberFormatException {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.NUMERIC == ct) {
			return cell.getNumericCellValue();
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.STRING == ct) {
			String ss = cell.getStringCellValue();
			return ss.length() != 0 ? Double.valueOf(ss) : null;
		} else if (CellType.FORMULA == ct) {
			CellType mct = cell.getCachedFormulaResultType();
			if (CellType.NUMERIC == mct) {
				return cell.getNumericCellValue();
			} else if (CellType.STRING == mct) {
				String ss = cell.getStringCellValue();
				return ss.length() != 0 ? Double.valueOf(ss) : null;
			} else if (CellType.BOOLEAN == mct) {
				throw new NumberFormatException("布尔值不是数字类型");
			} else {
				throw new NumberFormatException("计算失败结果不是数字类型");
			}
		} else {
			throw new NumberFormatException("内部错误类型不是数字类型");
		}
	}

	public static double getCellDoublePrimitiveValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.doubleValue() : 0D;
	}

	public static Float getCellFloatValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.floatValue() : null;
	}

	public static float getCellFloatPrimitiveValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.floatValue() : 0F;
	}

	public static Long getCellLongValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.longValue() : null;
	}

	public static long getCellLongPrimitiveValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.longValue() : 0L;
	}

	public static Integer getCellIntegerValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.intValue() : null;
	}

	public static int getCellIntPrimitiveValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.intValue() : 0;
	}

	public static Short getCellShortValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.shortValue() : null;
	}

	public static short getCellShortPrimitiveValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.shortValue() : 0;
	}

	public static Byte getCellByteValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.byteValue() : null;
	}

	public static byte getCellBytePrimitiveValue(Cell cell) throws NumberFormatException {
		Double vv = getCellDoubleValue(cell);
		return vv != null ? vv.byteValue() : 0;
	}

	/**
	 * 获取单元格布尔值内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为布尔值，非布尔值文本则抛出非布尔值异常；</li>
	 * <li>数字类型直接抛出非布尔值异常；</li>
	 * <li>错误类型直接抛出非布尔值异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 布尔值
	 * @throws BooleanParseException 非布尔值或者文本解析布尔值失败
	 */
	public static Boolean getCellBooleanValue(Cell cell) throws BooleanParseException {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.BOOLEAN == ct) {
			return cell.getBooleanCellValue();
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.STRING == ct) {
			String ss = cell.getStringCellValue();
			if ("true".equals(ss)) {
				return Boolean.TRUE;
			} else if ("false".equals(ss)) {
				return Boolean.FALSE;
			} else if (ss.length() == 0) {
				return null;
			} else {
				throw new BooleanParseException();
			}
		} else if (CellType.FORMULA == ct) {
			CellType mct = cell.getCachedFormulaResultType();
			if (CellType.BOOLEAN == mct) {
				return cell.getBooleanCellValue();
			} else if (CellType.STRING == mct) {
				String ss = cell.getStringCellValue();
				if ("true".equals(ss)) {
					return Boolean.TRUE;
				} else if ("false".equals(ss)) {
					return Boolean.FALSE;
				} else if (ss.length() == 0) {
					return null;
				} else {
					throw new BooleanParseException();
				}
			} else if (CellType.NUMERIC == mct) {
				throw new BooleanParseException("数字不是布尔类型");
			} else {
				throw new BooleanParseException("计算失败结果不是布尔类型");
			}
		} else if (CellType.NUMERIC == ct) {
			throw new BooleanParseException("数字不是布尔类型");
		} else {
			throw new NumberFormatException("内部错误类型不是数字类型");
		}
	}

	public static boolean getCellBooleanPrimitiveValue(Cell cell) throws BooleanParseException {
		Boolean vv = getCellBooleanValue(cell);
		return vv != null ? vv.booleanValue() : false;
	}

	/**
	 * 获取单元格时区日期值内容（天精度）
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 时区日期
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static Timestamp getCellDateValue(Cell cell) throws DateTimeException {
		return getCellDateValue(cell, DateUtils.defaultZoneOffset);
	}

	/**
	 * 获取单元格时区日期值内容（天精度）
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell   单元格
	 * @param offset 时区偏移，非空值。
	 * @return 时区日期
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static Timestamp getCellDateValue(Cell cell, ZoneOffset offset) throws DateTimeException {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.NUMERIC == ct) {
			return new Timestamp(DateUtils.toDayEpochMilli((long) cell.getNumericCellValue(), offset));
		} else if (CellType.STRING == ct) {
			String ss = cell.getStringCellValue();
			if (ss.length() == 0) {
				return null;
			}
			LocalDate ldate = LocalDate.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_EX);
			return DateUtils.toZoneTimestamp(ldate, offset);
		} else if (CellType.FORMULA == ct) {
			CellType mct = cell.getCachedFormulaResultType();
			if (CellType.NUMERIC == mct) {
				return new Timestamp(DateUtils.toDayEpochMilli((long) cell.getNumericCellValue(), offset));
			} else if (CellType.STRING == mct) {
				String ss = cell.getStringCellValue();
				if (ss.length() == 0) {
					return null;
				}
				LocalDate ldate = LocalDate.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_EX);
				return DateUtils.toZoneTimestamp(ldate, offset);
			} else if (CellType.BOOLEAN == mct) {
				throw new DateTimeException("布尔值不是日期类型");
			} else {
				throw new DateTimeException("计算失败结果不是日期类型");
			}
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.BOOLEAN == ct) {
			throw new DateTimeException("布尔值不是日期类型");
		} else {
			throw new DateTimeException("失败结果不是日期类型");
		}
	}

	/**
	 * 获取单元格时区日期时间值内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 时区日期时间
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static Timestamp getCellDateTimeValue(Cell cell) throws DateTimeException {
		return getCellDateTimeValue(cell, DateUtils.defaultZoneOffset);
	}

	/**
	 * 获取单元格时区日期时间值内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell   单元格
	 * @param offset 时区偏移，非空值。
	 * @return 时区日期时间
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static Timestamp getCellDateTimeValue(Cell cell, ZoneOffset offset) throws DateTimeException {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.NUMERIC == ct) {
			return new Timestamp((long) cell.getNumericCellValue());
		} else if (CellType.STRING == ct) {
			String ss = cell.getStringCellValue();
			if (ss.length() == 0) {
				return null;
			}
			LocalDateTime ldate = LocalDateTime.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_CN);
			return DateUtils.toZoneTimestamp(ldate, offset);
		} else if (CellType.FORMULA == ct) {
			CellType mct = cell.getCachedFormulaResultType();
			if (CellType.NUMERIC == mct) {
				return new Timestamp((long) cell.getNumericCellValue());
			} else if (CellType.STRING == mct) {
				String ss = cell.getStringCellValue();
				if (ss.length() == 0) {
					return null;
				}
				LocalDateTime ldate = LocalDateTime.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_CN);
				return DateUtils.toZoneTimestamp(ldate, offset);
			} else if (CellType.BOOLEAN == mct) {
				throw new DateTimeException("布尔值不是日期时间类型");
			} else {
				throw new DateTimeException("计算失败结果不是日期时间类型");
			}
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.BOOLEAN == ct) {
			throw new DateTimeException("布尔值不是日期时间类型");
		} else {
			throw new DateTimeException("失败结果不是日期时间类型");
		}
	}

	/**
	 * 获取单元格本地日期值内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 本地日期
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static LocalDate getCellLocalDateValue(Cell cell) throws DateTimeException {
		return getCellLocalDateValue(cell, DateUtils.defaultZoneOffset);
	}

	/**
	 * 获取单元格本地日期值内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell   单元格
	 * @param offset 时区偏移，非空值。
	 * @return 本地日期
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static LocalDate getCellLocalDateValue(Cell cell, ZoneOffset offset) throws DateTimeException {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.NUMERIC == ct) {
			return DateUtils.toLocalDate((long) cell.getNumericCellValue(), offset);
		} else if (CellType.STRING == ct) {
			String ss = cell.getStringCellValue();
			if (ss.length() == 0) {
				return null;
			}
			return LocalDate.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_EX);
		} else if (CellType.FORMULA == ct) {
			CellType mct = cell.getCachedFormulaResultType();
			if (CellType.NUMERIC == mct) {
				return DateUtils.toLocalDate((long) cell.getNumericCellValue(), offset);
			} else if (CellType.STRING == mct) {
				String ss = cell.getStringCellValue();
				if (ss.length() == 0) {
					return null;
				}
				return LocalDate.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_EX);
			} else if (CellType.BOOLEAN == mct) {
				throw new DateTimeException("布尔值不是无时区日期类型");
			} else {
				throw new DateTimeException("计算失败结果不是无时区日期类型");
			}
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.BOOLEAN == ct) {
			throw new DateTimeException("布尔值不是无时区日期类型");
		} else {
			throw new DateTimeException("失败结果不是无时区日期类型");
		}
	}

	/**
	 * 获取单元格本地日期时间值内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell 单元格
	 * @return 本地日期时间
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static LocalDateTime getCellLocalDateTimeValue(Cell cell) throws DateTimeException {
		return getCellLocalDateTimeValue(cell, DateUtils.defaultZoneOffset);
	}

	/**
	 * 获取单元格本地日期时间值内容
	 * <ul>
	 * <li>空白单元格直接返回空值；</li>
	 * <li>字符串类型自动解析为时区日期值，解释则抛出非日期时间异常；</li>
	 * <li>布尔类型、错误类型直接抛出非日期时间异常；</li>
	 * <li>计算单元则返回计算后结果；</li>
	 * </ul>
	 * 
	 * @param cell   单元格
	 * @param offset 时区偏移，非空值。
	 * @return 本地日期时间
	 * @throws DateTimeException 非数字值或者文本值解析日期时失败
	 */
	public static LocalDateTime getCellLocalDateTimeValue(Cell cell, ZoneOffset offset) throws DateTimeException {
		if (cell == null) {
			return null;
		}
		CellType ct = cell.getCellType();
		if (CellType.NUMERIC == ct) {
			return DateUtils.toLocalDateTime((long) cell.getNumericCellValue(), offset);
		} else if (CellType.STRING == ct) {
			String ss = cell.getStringCellValue();
			if (ss.length() == 0) {
				return null;
			}
			return LocalDateTime.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_EX);
		} else if (CellType.FORMULA == ct) {
			CellType mct = cell.getCachedFormulaResultType();
			if (CellType.NUMERIC == mct) {
				return DateUtils.toLocalDateTime((long) cell.getNumericCellValue(), offset);
			} else if (CellType.STRING == mct) {
				String ss = cell.getStringCellValue();
				if (ss.length() == 0) {
					return null;
				}
				return LocalDateTime.parse(ss, DateUtils.ISO_LOCAL_DATE_TIME_EX);
			} else if (CellType.BOOLEAN == mct) {
				throw new DateTimeException("布尔值不是无时区日期时间类型");
			} else {
				throw new DateTimeException("计算失败结果不是无时区日期时间类型");
			}
		} else if (CellType.BLANK == ct) {
			return null;
		} else if (CellType.BOOLEAN == ct) {
			throw new DateTimeException("布尔值不是无时区日期时间类型");
		} else {
			throw new DateTimeException("失败结果不是无时区日期时间类型");
		}
	}

}
